﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using System.Data.Common;
using System.Data;
using System.Collections;

namespace AMStudio.Common
{
    internal class ProductTagDAL
    {
        //private DataSet myDataset;

        public ProductTagDAL()
        {
            //myDataset = new DataSet();
            //Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MachineConfig");
            //string sql = "select * from [users$]";
            //DbCommand cmd = db.GetSqlStringCommand(sql);

            //string productsTable = "[users$]";

            //// Retrieve the initial data.
            //db.LoadDataSet(cmd, myDataset, productsTable);

            //// Get the table that will be modified.
            //DataTable dTable = myDataset.Tables[productsTable];

            

        }
        public bool Add(User item)
        {
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            StringBuilder sb = new StringBuilder();
            sb.Append(
                "insert into users values(@UserName,@FullName,@Password,@AuthorizationSet,@AuthorizationComments,@Enabled,@Language,@Comments)");
            DbCommand cmd = db.GetSqlStringCommand(sb.ToString());

            db.AddInParameter(cmd, "@UserName", DbType.String, item.UserName);
            db.AddInParameter(cmd, "@FullName", DbType.String, item.FullName);
            db.AddInParameter(cmd, "@Password", DbType.String, item.Password);
            db.AddInParameter(cmd, "@AuthorizationSet", DbType.Int32, item.AuthorizationSet);
            db.AddInParameter(cmd, "@AuthorizationComments", DbType.String, item.AuthorizationComments);
            db.AddInParameter(cmd, "@Enabled", DbType.Int32, item.Enabled);
            db.AddInParameter(cmd, "@Language", DbType.String, item.Language);
            db.AddInParameter(cmd, "@Comments", DbType.String, item.Comments);
            return db.ExecuteNonQuery(cmd) > 0 ? true : false;
        }
        
        public bool Update(User item)
        {
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            StringBuilder sb = new StringBuilder();
            sb.Append("update users set ");
            //sb.Append("ControlName=@ControlName,");
            sb.Append("[FullName]=@FullName,");
            sb.Append("[Password]=@Password,");
            sb.Append("[AuthorizationSet]=@AuthorizationSet,");
            sb.Append("[AuthorizationComments]=@AuthorizationComments,");
            sb.Append("[Enabled]=@Enabled,");
            sb.Append("[Language]=@Language,");
            sb.Append("[Comments]=@Comments ");
            sb.Append(" where [UserName]=@UserName");
            DbCommand cmd = db.GetSqlStringCommand(sb.ToString());
            db.AddInParameter(cmd, "@UserName", DbType.String, item.UserName);
            db.AddInParameter(cmd, "@FullName", DbType.String, item.FullName);
            db.AddInParameter(cmd, "@Password", DbType.String, item.Password);
            db.AddInParameter(cmd, "@AuthorizationSet", DbType.Int32, item.AuthorizationSet);
            db.AddInParameter(cmd, "@AuthorizationComments", DbType.String, item.AuthorizationComments);
            db.AddInParameter(cmd, "@Enabled", DbType.Int32, item.Enabled);
            db.AddInParameter(cmd, "@Language", DbType.String, item.Language);
            db.AddInParameter(cmd, "@Comments", DbType.String, item.Comments);
            int r = db.ExecuteNonQuery(cmd);
            return r> 0 ? true : false;;
        }

        public bool Delete(string userName)
        {
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            StringBuilder sb = new StringBuilder();
            sb.Append("delete from users ");
            sb.Append(" where UserName=@UserName");
            DbCommand cmd = db.GetSqlStringCommand(sb.ToString());
            db.AddInParameter(cmd, "@UserName", DbType.String, userName);
            return db.ExecuteNonQuery(cmd) > 0 ? true : false;
        }

        /// <summary>
        /// 查询所有信息
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, User> SelectAllMapper()
        {
            var list = new Dictionary<string,User>();
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            DataAccessor<User> myAccessor;
            myAccessor = db.CreateSqlStringAccessor("select * from [users]",
                MapBuilder<User>.MapAllProperties().
                Build()
                );
            list = myAccessor.Execute()
                .ToDictionary<User,string>
                ((item)=>
                    {
                        return item.UserName.Trim();
                    });
            return list;
        }
    }
}
